﻿using Oracle.ManagedDataAccess.Client;  // 引入 Oracle 数据库访问的命名空间
using System;  // 引入基础类库命名空间
using System.Data;  // 引入数据处理的命名空间

namespace Bank_database_system  // 定义命名空间 Bank_database_system
{
    public class Safe_Box_Operations  // 定义类 Safe_Box_Operations
    {
        // 定义数据库连接字符串作为常量
        private const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=47.96.39.153)(PORT=1521))(CONNECT_DATA=(SID=orcl)));
                                                 User Id=system;
                                                 Password=Tongji123456;
                                                 Connection Timeout=5;";

        public static DataTable InquirySafeBox(string page, string branchid, string boxsize, string boxstate, string safeboxid)
        {
            // 返回的表
            DataTable dataTable = new DataTable();
            int pageNumber;

            if (!int.TryParse(page, out pageNumber) || pageNumber < 1)
            {
                MessageBox.Show("页面编号无效", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return new DataTable();
            }

            int startRow = (pageNumber - 1) * 10 + 1;
            int endRow = pageNumber * 10;

            bool branchidDemand = false;
            bool boxsizeDemand = false;
            bool boxstateDemand = false;
            bool safeboxidDemand = false;

            // 数据库操作
            string selectQuery = @"
    SELECT * FROM (
    SELECT ROWNUM rnum, SAFE_BOX_ID, BRANCH_ID, BOX_SIZE, STATE
    FROM SAFE_BOX
    WHERE 1=1";

            if (!string.IsNullOrEmpty(branchid))
            {
                branchidDemand = true;
                selectQuery += " AND BRANCH_ID = :branchid";
            }

            if (!string.IsNullOrEmpty(boxsize))
            {
                boxsizeDemand = true;
                selectQuery += " AND BOX_SIZE = :boxsize";
            }

            if (!string.IsNullOrEmpty(boxstate))
            {
                boxstateDemand = true;
                selectQuery += " AND STATE = :boxstate";
            }

            if (!string.IsNullOrEmpty(safeboxid))
            {
                safeboxidDemand = true;
                selectQuery += " AND SAFE_BOX_ID = :safeboxid";
            }

            selectQuery += @") WHERE rnum BETWEEN :startRow AND :endRow";

            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {
                        // 添加参数
                        if (branchidDemand)
                        {
                            command.Parameters.Add("branchid", OracleDbType.Varchar2).Value = branchid;
                        }
                        if (boxsizeDemand)
                        {
                            command.Parameters.Add("boxsize", OracleDbType.Varchar2).Value = boxsize;
                        }
                        if (boxstateDemand)
                        {
                            command.Parameters.Add("boxstate", OracleDbType.Varchar2).Value = boxstate;
                        }
                        if (safeboxidDemand)
                        {
                            command.Parameters.Add("safeboxid", OracleDbType.Varchar2).Value = safeboxid;
                        }
                        command.Parameters.Add("startRow", OracleDbType.Int32).Value = startRow;
                        command.Parameters.Add("endRow", OracleDbType.Int32).Value = endRow;

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                dataTable.Load(reader); // 将查询结果加载到 DataTable

                            }
                            return dataTable;
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return new DataTable();
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return new DataTable();
            }
        }



        public static DataTable SelectSafeBox(string safeBoxId)
        {
            DataTable safeBoxTable = new DataTable();
            // 使用 OracleConnection 对象建立数据库连接
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    // 打开数据库连接
                    connection.Open();

                    // 定义 SQL 查询语句
                    string query = "SELECT SAFE_BOX_ID, BRANCH_ID, BOX_SIZE, BOX_CASH_PLEDGE, RENTAL_AMOUNT, STATE FROM SAFE_BOX WHERE SAFE_BOX_ID = :safeBoxId";

                    // 使用 OracleCommand 对象执行 SQL 查询
                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        // 添加参数并设置其值和类型
                        command.Parameters.Add(":safeBoxId", OracleDbType.Varchar2).Value = safeBoxId;

                        // 使用 OracleDataAdapter 填充数据表
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            adapter.Fill(safeBoxTable);  // 填充数据表
                        }
                    }
                }
                catch (OracleException ex)  // 捕获并处理 Oracle 数据库相关异常
                {
                    // 输出错误信息
                    Console.WriteLine("Oracle error occurred: " + ex.Message);
                }
                catch (Exception ex)  // 捕获并处理其他类型的异常
                {
                    // 输出错误信息到控制台
                    Console.WriteLine("An error occurred: " + ex.Message);
                }
            }

            return safeBoxTable;
        }


        // 调用 HIRE_SAFE_BOX SQL 函数的方法
        public static string HireSafeBox(
            string hireCreditCardNumbers,//5
            string safeBoxHireId,//1
            decimal hireDuration,//text8
            decimal boxCashPledge,//7
            decimal hireRentalAmount,//4
            string hireStaffId,
            string ip)
        {
            string result = null;

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    // 打开数据库连接
                    connection.Open();

                    // 定义调用存储函数的 SQL 语句
                    string functionCall = "HIRE_SAFE_BOX";

                    // 使用 OracleCommand 对象调用存储函数
                    using (OracleCommand command = new OracleCommand(functionCall, connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        // 添加返回值参数
                        OracleParameter returnValue = new OracleParameter("return_value", OracleDbType.Varchar2, 2000)
                        {
                            Direction = ParameterDirection.ReturnValue
                        };
                        command.Parameters.Add(returnValue);

                        // 添加输入参数并设置其值
                        command.Parameters.Add("HIRE_CREDIT_CARD_NUMBERS", OracleDbType.Varchar2).Value = hireCreditCardNumbers;
                        command.Parameters.Add("SAFE_BOX_HIRE_ID", OracleDbType.Varchar2).Value = safeBoxHireId;
                        command.Parameters.Add("HIRE_DURATION", OracleDbType.Decimal).Value = hireDuration;
                        command.Parameters.Add("BOX_CASH_PLEDGE", OracleDbType.Decimal).Value = boxCashPledge;
                        command.Parameters.Add("HIRE_RENTAL_AMOUNT", OracleDbType.Decimal).Value = hireRentalAmount;
                        command.Parameters.Add("HIRE_STAFF_ID", OracleDbType.Varchar2).Value = hireStaffId;
                        command.Parameters.Add("IP", OracleDbType.Varchar2).Value = ip;
                        // 执行存储过程
                        command.ExecuteNonQuery();
                        // 获取返回值
                        result = returnValue.Value.ToString();
                    }
                }
                catch (Exception ex)
                {
                    // 输出错误信息到控制台
                    Console.WriteLine("An error occurred: " + ex.Message);
                    MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return "An error occurred: " + ex.Message;
                }
            }

            return result;  // 返回存储函数的返回值
        }
        //读取个人保险箱的函数
        public static DataTable GetSafeBoxRecords(string page, string begin_time, string end_time, string creditCardNumbers)
        {
            DataTable safeBoxRecords = new DataTable();
            int pageNumber;

            if (!int.TryParse(page, out pageNumber) || pageNumber < 1)
            {
                MessageBox.Show("页面编号无效", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return new DataTable();
            }

            int pageSize = 10;
            int startRow = (pageNumber - 1) * pageSize + 1;
            int endRow = pageNumber * pageSize;

            DateTime BeginTime;
            DateTime EndTime;

            try
            {
                BeginTime = DateTime.Parse(begin_time + " 00:00:00");
                EndTime = DateTime.Parse(end_time + " 23:59:59");
            }
            catch (FormatException ex)
            {
                MessageBox.Show($"日期格式无效: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return new DataTable();
            }

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = @"
SELECT * FROM (
    SELECT 
        sb.SAFE_BOX_ID, 
        sbh.HIRE_ID, 
        sbh.BEGIN_TIME, 
        sbh.END_TIME, 
        sbh.STATE AS HIRE_STATE,
        ROW_NUMBER() OVER (ORDER BY sbh.BEGIN_TIME DESC) AS ROWNUMBER
    FROM SAFE_BOX sb
    INNER JOIN SAFE_BOX_HIRE sbh ON sb.SAFE_BOX_ID = sbh.SAFE_BOX_ID
    WHERE sbh.CREDIT_CARD_NUMBERS = :CREDIT_CARD_NUMBERS";

                    if (!string.IsNullOrEmpty(begin_time))
                    {
                        query += " AND sbh.BEGIN_TIME >= :BeginTime";
                    }

                    if (!string.IsNullOrEmpty(end_time))
                    {
                        query += " AND sbh.END_TIME <= :EndTime";
                    }

                    query += @") WHERE ROWNUMBER BETWEEN :StartRow AND :EndRow";

                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        command.Parameters.Add("CREDIT_CARD_NUMBERS", OracleDbType.Varchar2).Value = creditCardNumbers;

                        if (!string.IsNullOrEmpty(begin_time))
                        {
                            command.Parameters.Add("BeginTime", OracleDbType.Date).Value = BeginTime;
                        }

                        if (!string.IsNullOrEmpty(end_time))
                        {
                            command.Parameters.Add("EndTime", OracleDbType.Date).Value = EndTime;
                        }

                        command.Parameters.Add("StartRow", OracleDbType.Int32).Value = startRow;
                        command.Parameters.Add("EndRow", OracleDbType.Int32).Value = endRow;

                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            adapter.Fill(safeBoxRecords);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    Console.WriteLine("An error occurred: " + ex.Message);
                }
            }

            return safeBoxRecords;
        }

        //获得指定保险箱的数量
        public static int GetSafeBoxCount(string branchid, string boxsize, string boxstate, string safeboxid)
        {
            int count = 0;
            bool branchidDemand = false;
            bool boxsizeDemand = false;
            bool boxstateDemand = false;
            bool safeboxidDemand = false;

            string selectQuery = @"
        SELECT COUNT(*) OVER() AS TotalCount
        FROM SAFE_BOX
        WHERE 1=1";

            if (!string.IsNullOrEmpty(branchid))
            {
                branchidDemand = true;
                selectQuery += " AND BRANCH_ID = :branchid";
            }

            if (!string.IsNullOrEmpty(boxsize))
            {
                boxsizeDemand = true;
                selectQuery += " AND BOX_SIZE = :boxsize";
            }

            if (!string.IsNullOrEmpty(boxstate))
            {
                boxstateDemand = true;
                selectQuery += " AND STATE = :boxstate";
            }

            if (!string.IsNullOrEmpty(safeboxid))
            {
                safeboxidDemand = true;
                selectQuery += " AND SAFE_BOX_ID = :safeboxid";
            }

            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {
                        if (branchidDemand)
                        {
                            command.Parameters.Add("branchid", OracleDbType.Varchar2).Value = branchid;
                        }
                        if (boxsizeDemand)
                        {
                            command.Parameters.Add("boxsize", OracleDbType.Varchar2).Value = boxsize;
                        }
                        if (boxstateDemand)
                        {
                            command.Parameters.Add("boxstate", OracleDbType.Varchar2).Value = boxstate;
                        }
                        if (safeboxidDemand)
                        {
                            command.Parameters.Add("safeboxid", OracleDbType.Varchar2).Value = safeboxid;
                        }

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                count = Convert.ToInt32(reader["TotalCount"]);
                            }
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return count;
        }
        //获得带有时间限制的个人的保险箱数量
        public static int GetSafeBoxRecordCount(string creditCardNumbers, string begin_time, string end_time)
        {
            int recordCount = 0;

            // 定义日期变量
            DateTime BeginTime;
            DateTime EndTime;

            // 解析日期并处理格式异常
            try
            {
                if (!string.IsNullOrEmpty(begin_time))
                {
                    BeginTime = DateTime.Parse(begin_time + " 00:00:00");
                }
                else
                {
                    BeginTime = DateTime.MinValue;  // 设定一个合理的默认值
                }

                if (!string.IsNullOrEmpty(end_time))
                {
                    EndTime = DateTime.Parse(end_time + " 23:59:59");
                }
                else
                {
                    EndTime = DateTime.MaxValue;  // 设定一个合理的默认值
                }
            }
            catch (FormatException ex)
            {
                MessageBox.Show($"日期格式无效: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0; // 返回 0 表示未能正确处理日期
            }

            string query = @"
    SELECT COUNT(*) 
    FROM SAFE_BOX sb
    INNER JOIN SAFE_BOX_HIRE sbh ON sb.SAFE_BOX_ID = sbh.SAFE_BOX_ID
    WHERE sbh.CREDIT_CARD_NUMBERS = :CREDIT_CARD_NUMBERS";

            if (BeginTime != DateTime.MinValue)
            {
                query += " AND sbh.BEGIN_TIME >= :BeginTime";
            }

            if (EndTime != DateTime.MaxValue)
            {
                query += " AND sbh.END_TIME <= :EndTime";
            }

            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        command.Parameters.Add("CREDIT_CARD_NUMBERS", OracleDbType.Varchar2).Value = creditCardNumbers;

                        if (BeginTime != DateTime.MinValue)
                        {
                            command.Parameters.Add("BeginTime", OracleDbType.Date).Value = BeginTime;
                        }

                        if (EndTime != DateTime.MaxValue)
                        {
                            command.Parameters.Add("EndTime", OracleDbType.Date).Value = EndTime;
                        }

                        recordCount = Convert.ToInt32(command.ExecuteScalar());
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return recordCount;
        }

    }
}
